<?php
class Data
{
        const _CONST_TB_STATS_ = 'oct_data_stats';
        const _CONST_TB_DAILY_ = 'oct_data_daily';
	    const _CONST_TB_REPORT_ = 'oct_data_report';
        const _CONST_TB_RECORD_ = 'oct_data_records';
        const _CONST_TB_SDLOGS_ = 'oct_data_sdlogs';

        public static function stats_limit( $query, $offset, $limit)
        {
//            $search = new Util_Search();
//            $query['order_by'] = array('day' => 'DESC');
//            $result = $search->search($query, $offset, $limit);
////            var_dump($result);exit;
            $result  = DB::select('*')
                        ->from('oct_data_stats')
                        ->where('brand_chn', '!=', '0')
                        ->where('types_chn', '!=', '0')
                        ->where('imei_chn', '!=', '0')
                        ->offset($offset)
                        ->limit($limit)
                        ->order_by('day', 'DESC')
                        ->execute();
//                var_dump($result);exit;
            
            $records = array();
            foreach ($result as $rs)
            {
                    $dianmian  = DB::select('*')
                        ->from('user_basic')
                        ->where('uid', '=', $rs['uid'])
//                        ->where('status', '=', 1)
                        ->limit(1)
                        ->execute()
                        ->current();

//                if(!$dianmian)                    continue;
                    $day = $rs['day'];
                    $day = date("Y-m-d", strtotime($day));
                    $records[] = array(
                            'uid' => $rs['uid'],
                            'day' => $day,
                            'mobiles_chn' => $rs['imei_chn'],
                            'brand_chn'   => $rs['brand_chn'],
                            'types_chn'   => $rs['types_chn'],
                            'nickname'=> $dianmian['nickname'],
                            'realname'=> $dianmian['realname'],
                            'passport'=> $dianmian['passport'],
                    );
            }
            
            $count  = DB::select(array('count("imei_chn")', 'count'))
                ->from('oct_data_stats')
                ->where('imei_chn', '!=', 0)
                ->limit(1)
                ->execute()
                ->get('count');
            
            return array('count'=> $count,'list' =>$records);
        }
        
        public static function stats ($uid, $limit = NULL, $start_date, $end_date,$machine)
        {
                $start_date = str_replace('/','',$start_date);
                $end_date   = str_replace('/','',$end_date);

                $uids = array($uid);
                $query = DB::select('uid')
                    ->from('oct_data_config')
                    ->where('parent', '=', $uid)
                    ->execute();
                foreach ($query as $rs)
                {
                    $uids[] = $rs['uid'];
                }

                $query = DB::select('s.*', 'u.nickname', 'u.passport', 'u.realname', 'u.uid')
                        ->from(array(self::_CONST_TB_STATS_, 's'))
                        ->join(array('user_basic', 'u'), 'LEFT')
                        ->on('s.uid', '=', 'u.uid')
                        ->where('s.uid', 'in', $uids)
                        ->where('s.day','>=',$start_date)
                        ->where('s.day','<=',$end_date)
                        ->where('u.utype', '=', 'merchant')
                        ->where('u.status', '=', 1)
                        ->order_by('day', 'DESC');

                if (NULL !== $limit)
                    $query = $query->limit((int) $limit)
                                ->offset(0);

                if ($machine != 'all' && $machine!= null && machine::machine2uid($machine)!=0)
                    $query = $query ->where('u.uid', '=', machine::machine2uid($machine));

                $query = $query->execute();

                $records = array();
                foreach ($query as $rs)
                {
                        $day = $rs['day'];
                        $day = date("Y-m-d", strtotime($day));
                        $records[] = array(
                                'uid' => $rs['uid'],
                                'day' => $day,
                                'mobiles' => $rs['imei'],
                                'brand'   => $rs['brand'],
                                'types'   => $rs['types'],
                                'mobiles_chn' => $rs['imei_chn'],
                                'brand_chn'   => $rs['brand_chn'],
                                'types_chn'   => $rs['types_chn'],
                                'nickname'=> $rs['nickname'],
                                'realname'=> $rs['realname'],
                                'passport'=> $rs['passport'],
                        );
                }

                return $records;
        }

        public static function daily ($day, $uid = NULL)
        {
                $query = DB::select('d.*', 'u.passport', array('u.comments', 'dianmian'))
                        ->from(array('user_basic', 'u'))
                        ->join(array('oct_data_daily', 'd'), "LEFT")
                        ->on('u.uid', '=', 'd.uid')
                        ->where('d.is_public','=','1')
                        ->where('d.day', '=', $day)
                        ->where('u.status', '=', 1);

                if (!empty($uid))
                        $query->where('d.uid', '=', (int) $uid);

                $query = $query->execute();
                
                $result = array();
                $result['sum_count'] = 0;
                foreach ($query as $v) {
                    $result['sum_count'] = $result['sum_count'] + $v['apps'];
                    $result['list'][] = $v;
                }
//                var_dump($result);exit;
                return $result;
        }
        
         public static function apps($imei, $day, $uid)
        {
             $query = DB::select(array('d.machine', 'machine'))
                        ->from(array('user_basic', 'u'))
                        ->join(array('oct_data_config', 'd'), "LEFT")
                        ->on('u.uid', '=', 'd.uid')
                        ->where('u.uid', '=', $uid)
                        ->where('u.status', '=', 1)
                        ->execute()
                        ->as_array();

            $result = DB::select('*')
                    ->from(self::_CONST_TB_RECORD_)
                    ->where('IMEI', '=', $imei)
                    ->where('day',  '=', $day)
                    ->where('machine', '=', $query[0]['machine'])
                    ->order_by('mtime','asc')
                    ->execute()
                    ->as_array();
            return $result;
        }

	public static function valid ($data)
	{
		$token   = 'Ks22@456U';
		$machine = Arr::get($data, 'machine');	
		$auth    = Arr::get($data, 'auth');
		if (empty($machine) || empty($auth))
			return FALSE;

		unset($data['machine']);
		unset($data['auth']);
		unset($data['content']);
		uksort($data, function ($a, $b) { 
			$a1 = (int) substr($a, 1); $b1 = (int) substr($b, 1); return $a1 > $b1;
		});
		$str = "machine=$machine";
		foreach ($data as $key => $val)
		{
			$str .= "$key=$val";
		}

		$str .= $token;
		$md5  = md5($str);
		return $md5 == $auth;
	}

	public static function record_report ($post)
	{
		$id = Arr::get($post, 'machine');

		$items = array();
		foreach ($post as $key => $val)
		{
			if (preg_match('/^d\d+$/', $key))
			{
				$items[] = json_decode($val);
			}
		}
	
		$fh = fopen('/tmp/logs', 'a');
		foreach ($items as $k => $v)
		{
			fwrite($fh, "$id," . join(',', $v) . "\n");
		}
		fclose($fh);

        self::record_db_report($post);
	}

	public static function record_db_report ($post)
	{
		$id = Arr::get($post, 'machine');

		$items = array();
		foreach ($post as $key => $val)
		{
			if (preg_match('/^d\d+$/', $key))
			{
				$items[] = json_decode($val);
			}
		}
	
        $days = array();
		foreach ($items as $k => $v)
		{
            $data = array(
                'machine' => $id,
                'types'   => $v[1],
                'mtime'   => $v[2],
                'IMEI'    => $v[3],
                'pname'   => $v[4],
                'status'  => $v[5],
                'jobNO'   => $v[6],
                'code'    => $v[7],
                'day'     => $v[8],
                'ctime'   => DB::expr('unix_timestamp(now())'),
            );

            if (empty($v[3]) || empty($v[4]))
                continue;

            $count = DB::select(array('count("IMEI")', 'count'))
                    ->from(self::_CONST_TB_RECORD_)
                    ->where('IMEI', '=', $v[3])
                    ->where('pname', '=', $v[4])
                    ->execute()
                    ->get('count');

            if ($count > 0)
                continue;

            DB::insert(self::_CONST_TB_RECORD_)
                ->columns (array_keys($data))
                ->values  (array_values($data))
                ->execute();

            $days[$v[8]] = 1;
		}

        self::sync_daily($id, $days);
        self::sync_stats($id, $days);
	}

    public static function record_sdlogs_manualy($uid, $day, $records)
    {
        $id = machine::uid2machine($uid);
        $n  = 0;
		foreach ($records as $k => $v)
		{
            $data = array(
                'machine' => $id,
                'types'   => $v[1],
                'mtime'   => $v[2],
                'IMEI'    => $v[3],
                'pname'   => $v[4],
                'status'  => $v[5],
                'jobNO'   => $v[6],
                'code'    => $v[7],
                'day'     => $day,
                'ctime'   => DB::expr('unix_timestamp(now())'),
            );

            if (empty($v[3]) || empty($v[4]))
                continue;

            $count = DB::select(array('count("IMEI")', 'count'))
                    ->from(self::_CONST_TB_RECORD_)
                    ->where('IMEI', '=', $v[3])
                    ->where('pname', '=', $v[4])
                    ->execute()
                    ->get('count');

            if ($count > 0)
                continue;

            try {
            list($insert_id, $rows) = DB::insert(self::_CONST_TB_RECORD_)
                ->columns (array_keys($data))
                ->values  (array_values($data))
                ->execute();
            }
            catch (exception $e)
            {
                echo "ERROR: ".$e->getMessage()."\n";
                $insert_id = 0;
            }

            if ($insert_id > 0)
                $n++;
		}

        if ($n > 0)
        {
            self::sync_daily($id, array($day => 1));
            self::sync_stats($id, array($day => 1));
        }
    }

    public static function sync_daily($machine, $days)
    {
        if (empty($days))
            return ;

        $uid = machine::machine2uid($machine);
        if (empty($uid))
            return ;

        foreach ($days as $day => $val)
        {
            $query = DB::select('types', 'IMEI', array(DB::expr('count(distinct(pname))'), 'apps'))
                ->from(self::_CONST_TB_RECORD_)
                ->where('machine', '=', $machine)
                ->where('day', '=', $day)
                ->where('status', '=', 'Success')
                ->group_by('IMEI')
                ->execute();

            foreach ($query as $rs)
            {
                $types = $rs['types'];
                $imei  = $rs['IMEI'];
                $apps  = $rs['apps'];

                $query2 = DB::select('apps')
                    ->from(self::_CONST_TB_DAILY_)
                    ->where('uid', '=', $uid)
                    ->where('day', '=', $day)
                    ->where('IMEI', '=', $imei)
                    ->execute();

                if ($query2->count() > 0)
                {
                    DB::update(self::_CONST_TB_DAILY_)
                        ->set (array('apps' => $apps))
                        ->where('uid', '=', $uid)
                        ->where('day', '=', $day)
                        ->where('IMEI', '=', $imei)
                        ->execute();
                }
                else
                {
                    $info = array(
                        'brand' => $types,
                        'types' => $types,
                        'IMEI'  => $imei,
                        'apps'  => $apps,
                        'day'   => $day,
                        'uid'   => $uid,
                    );

                    DB::insert(self::_CONST_TB_DAILY_)
                        ->columns (array_keys($info))
                        ->values  (array_values ($info))
                        ->execute();
                }
            } // foreach

        } // foreach

    }

    // 
    // 为渠道商同步每日stats数据
    //
    public static function sync_stats_chn ($uid, $days)
    {
        $query = DB::select(
                array(DB::expr('count(distinct(brand))'), 'brand'),
                array(DB::expr('count(distinct(types))'), 'types'),
                array(DB::expr('count(distinct(IMEI))'), 'IMEI'),
                'day'
            )
            ->from(self::_CONST_TB_DAILY_)
            ->where('uid', '=', $uid)
            ->where('day', 'in', $days)
            ->where('is_public', '=', 1)
            ->group_by('day')
            ->execute();
                
        foreach ($query as $rs)
        {
            $day   = $rs['day'];
            $brand = $rs['brand'];
            $types = $rs['types'];
            $imei  = $rs['IMEI'];

            $query2 = DB::select('brand')
                ->from(self::_CONST_TB_STATS_)
                ->where('uid', '=', $uid)
                ->where('day', '=', $day)
                ->execute();

            if ($query2->count() > 0)
            {
                DB::update(self::_CONST_TB_STATS_)
                    ->set (array(
                            'brand_chn' => $brand,
                            'types_chn' => $types,
                            'imei_chn'  => $imei,
                        ))
                    ->where('uid', '=', $uid)
                    ->where('day', '=', $day)
                    ->execute();
            }
            else
            {
                $info = array(
                    'brand_chn' => $types,
                    'types_chn' => $types,
                    'imei_chn'  => $imei,
                    'day'   => $day,
                    'uid'   => $uid,
                );

                DB::insert(self::_CONST_TB_STATS_)
                    ->columns (array_keys($info))
                    ->values  (array_values ($info))
                    ->execute();

            } // if

        } //foreach
    }

    public static function sync_stats ($machine, $days)
    {
        if (empty($days))
            return;

        $uid = machine::machine2uid($machine);

        if (empty($uid))
            return ;

        $query = DB::select(
                array(DB::expr('count(distinct(brand))'), 'brand'),
                array(DB::expr('count(distinct(types))'), 'types'),
                array(DB::expr('count(distinct(IMEI))'), 'IMEI'),
                'day')
            ->from(self::_CONST_TB_DAILY_)
            ->where('uid', '=', $uid)
            ->where('day', 'in', array_keys($days))
            ->group_by('day')
            ->execute();
                
        foreach ($query as $rs)
        {
            $day   = $rs['day'];
            $brand = $rs['brand'];
            $types = $rs['types'];
            $imei  = $rs['IMEI'];

            $query2 = DB::select('brand')
                ->from(self::_CONST_TB_STATS_)
                ->where('uid', '=', $uid)
                ->where('day', '=', $day)
                ->execute();

            if ($query2->count() > 0)
            {
                DB::update(self::_CONST_TB_STATS_)
                    ->set (array(
                            'brand' => $brand,
                            'types' => $types,
                            'imei'  => $imei,
                        ))
                    ->where('uid', '=', $uid)
                    ->where('day', '=', $day)
                    ->execute();
            }
            else
            {
                $info = array(
                    'brand' => $types,
                    'types' => $types,
                    'imei'  => $imei,
                    'day'   => $day,
                    'uid'   => $uid,
                );

                DB::insert(self::_CONST_TB_STATS_)
                    ->columns (array_keys($info))
                    ->values  (array_values ($info))
                    ->execute();

            } // if

        } //foreach

    }

    public static function store_sd_logs ($uid, $name, $url, $location)
    {
        $data = array(
            'uid'  => $uid,
            'name' => $name,
            'url'  => $url,
            'location' => $location,
            'ctime' => DB::expr('unix_timestamp(now())'),
        );

        DB::insert(self::_CONST_TB_SDLOGS_)
            ->columns (array_keys($data))
            ->values (array_values($data))
            ->execute();
    }

    public static function sdlogs ($uid)
    {
        return DB::select()
            ->from(self::_CONST_TB_SDLOGS_)
            ->where('uid', '=', $uid)
            ->order_by('ctime', 'DESC')
            ->execute();
    }

}
